﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace datagridview
{
    class DataBase
    {
        public SqlConnection connection;
        public void open()
        {
            connection = new SqlConnection("Data Source=.;Initial Catalog=db_2012;Integrated Security=True");
            connection.Open();
        }
        public void close()
        {
            connection.Dispose();
            connection.Close();
            connection = null;
        }
        public SqlDataReader GetDataReader(string sqlstring)
        {
            open();
            SqlCommand mycom = new SqlCommand(sqlstring,connection);
            SqlDataReader Dr=mycom.ExecuteReader();
            return Dr;

        }
        public DataSet GetDataSet(string sqlstring)
        {
            open();
            SqlCommand mycom = new SqlCommand(sqlstring,connection);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = mycom;
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            close();
            return dataset;
        }
        public int ExecuteSQL(string sqlstring)
        {
            int count = -1;
            open();
            try
            {
                SqlCommand cmd=new SqlCommand(sqlstring,connection);
                count=cmd.ExecuteNonQuery();
            }
            catch
            {count=-1;
            }
            finally
            {
                close();
            }
            return count;
        }
        public int GetdataRow(string sqlstring)
        {
            int CountRow = 0;
            open();
            SqlCommand mycom = new SqlCommand(sqlstring, connection);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = mycom;
            DataSet ds = new DataSet();
            da.Fill(ds);
            ds.CaseSensitive = false;
            CountRow = ds.Tables[0].Rows.Count;
            close();
            return CountRow;
        }
        public DataTable GetDataTable(string sqlstring)
        {
            DataSet ds = new DataSet(sqlstring);
            DataTable dt = new DataTable();
            dt = ds.Tables[0];
            return dt;
        }
        public object GetScalar(string sqlstring)
        {
            open();
            SqlCommand mycom = new SqlCommand(sqlstring ,connection);
            object result = mycom.ExecuteScalar();
            close();
            return result;
        }


        public bool doUpdate(DataSet ds, string sql, string TableName)
        {
            bool flag = false;
            open();
            using (SqlDataAdapter da = new SqlDataAdapter(sql, connection))
            {
                SqlCommandBuilder builder = new SqlCommandBuilder(da);
                try
                {
                    lock (this)
                    {
                        da.Update(ds,TableName);
                        flag = true;
                    }
                }
                catch(SqlException e)
                {
                    throw new Exception(e.Message);
                }
            }
            close();
            return flag;
        }
        public ArrayList GetListArray(string sqlstring, int m)
        {
            ArrayList array = new ArrayList();
            SqlDataReader dr =GetDataReader(sqlstring);
            while (dr.Read())
            {
                array.Add(dr.GetValue(m));
            }
            return array;
        }
        public SqlCommand GetProcCommand(string sqlstring)
        {
            open();
            SqlCommand mycommand = new SqlCommand(sqlstring, connection);
            return mycommand;
        }
  
    }
}
